﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using prototype123.controllers;
using prototype123.models;
using System.Collections;

namespace prototype123.data_access
{
    //This class handles all data access between the application and the
    //course table in the database.
    class CourseDAO
    {
        private SqlConnection db_connection;

        public CourseDAO(SqlConnection connection)
        {
            db_connection = connection;
        }


        /// <precondition>System needs course information given a courseID</precondition>
        /// <summary>
        /// Returns all information about a given course
        /// </summary>
        /// <param name="course_id">The ID of the course we are looking up</param>
        /// <returns>Course object that contains all related information about a course</returns>
        /// <postconditions>System now has all information about a given course for eventual GUI population</postconditions>
        public Course GetCourse(string course_id)
        {

            Course course = new Course();
            SqlCommand getcourse = new SqlCommand("SELECT * FROM COURSE WHERE COURSE_ID = '" + course_id + "'", db_connection);
            SqlDataReader reader = getcourse.ExecuteReader();
            reader.Read();

            course.CourseID = (string)reader["COURSE_ID"];
            course.Credits = Convert.ToDouble(reader["CREDITS"]);
            course.ShortTitle = (string)reader["SHORT_TITLE"];
            course.SortSequence = Convert.ToInt32(reader["SORT_SEQ"]);
            course.Status = Convert.ToChar(reader["STATUS"]);
            course.Title = (string)reader["TITLE"];

            course.SubjectID = Convert.ToInt32(reader["SUBJECT_ID"]);
            course.TextbookID = Convert.ToInt32(reader["TEXTBOOK_ID"]);
            reader.Close();

            return course;
        }

        //Dersch is messing around with this alot. Caleb's code is commented out and obsolete and worthless
        //yeah well at least I don't use underscores and foreach loops and name functions "Find" and "Load"


        /// <preconditioN>Educator is logged in and has selected a student</preconditioN>
        /// <summary>
        /// Retrieves a list of course id's for all courses a given student is enrolled in
        /// </summary>
        /// <param name="stuid">ID# for the selected student</param>
        /// <param name="termid">ID# of the current term for the current student</param>
        /// <returns>ArrayList of string objects - each string containing a course_id</returns>
        /// <postcondition>System now has a list of course ID's for each course a student is taking</postcondition>
        public ArrayList GetStudentCourseList(int stuid, int termid)
        {

            ArrayList classes = new ArrayList();
            ArrayList result = new ArrayList();

            SqlCommand getid = new SqlCommand("SELECT COURSE_ID FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and TERM_ID = " + termid, db_connection);


            SqlDataReader read = getid.ExecuteReader();

            while (read.Read())
            {
                classes.Add(read[0].ToString());
            }

            read.Close();

            foreach (string courseid in classes)
            {
                Course nextcourse = GetCourse(courseid);
                result.Add(nextcourse);
            }

            return result;

        }
        

        /// <precondition>System needs a list of all possible courses</precondition>
        /// <summary>
        /// Retrieves a list of all courses a student could enroll in
        /// </summary>
        /// <returns>An ArrayList of Course objects - one for each course that can be taken</returns>
        /// <postcondition>System can now populate GUI with all possible courses</postcondition>
        public ArrayList LoadAllCourses()
        {
            ArrayList allcourses = new ArrayList();

            SqlCommand getcourse = new SqlCommand("SELECT * FROM COURSE", db_connection);
            SqlDataReader reader = getcourse.ExecuteReader();
            while (reader.Read())
            {
                Course course = new Course();
                course.CourseID = (string)reader["COURSE_ID"];
                course.Credits = Convert.ToDouble(reader["CREDITS"]);
                course.ShortTitle = (string)reader["SHORT_TITLE"];
                course.SortSequence = Convert.ToInt32(reader["SORT_SEQ"]);
                course.Status = Convert.ToChar(reader["STATUS"]);
                course.Title = (string)reader["TITLE"];
                course.SubjectID = Convert.ToInt32(reader["SUBJECT_ID"]);
                course.TextbookID = Convert.ToInt32(reader["TEXTBOOK_ID"]);
                allcourses.Add(course);
            }
            reader.Close();

            return allcourses;
        }
    }
}
